CREATE TABLE [dbo].[AppealMain]
(
[AppealKey] [uniqueidentifier] NOT NULL,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_AppealMain_Name] DEFAULT (''),
[Description] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_AppealMain_Description] DEFAULT (''),
[AppealStatusCode] [int] NOT NULL CONSTRAINT [DF_AppealMain_AppealStatusCode] DEFAULT ((0)),
[CostCollection] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TotalRevenue] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_AppealMain_TotalRevenue] DEFAULT ((0)),
[TargetRevenue] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_AppealMain_TargetRevenue] DEFAULT ((0)),
[PredictedResponseRate] [decimal] (5, 4) NOT NULL CONSTRAINT [DF_AppealMain_PredictedResponseRate] DEFAULT ((0)),
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[UpdatedOn] [datetime] NOT NULL,
[UpdatedByUserKey] [uniqueidentifier] NOT NULL,
[LowResponseAmount] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_AppealMain_LowResponseAmount] DEFAULT ((0)),
[HighResponseAmount] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_AppealMain_HighResponseAmount] DEFAULT ((0)),
[ActualCost] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_AppealMain_ActualCost] DEFAULT ((0)),
[EstimatedCost] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_AppealMain_EstimatedCost] DEFAULT ((0)),
[ExtendedCost] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_AppealMain_ExtendedCost] DEFAULT ((0)),
[OverheadCost] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_AppealMain_OverheadCost] DEFAULT ((0)),
[FirstResponseDate] [datetime] NULL,
[LastResponseDate] [datetime] NULL,
[TotalPositiveResponse] [int] NOT NULL CONSTRAINT [DF_AppealMain_TotalPositiveResponse] DEFAULT ((0)),
[TotalNegativeResponse] [int] NOT NULL CONSTRAINT [DF_AppealMain_TotalNegativeResponse] DEFAULT ((0)),
[TotalSolicited] [int] NOT NULL CONSTRAINT [DF_AppealMain_TotalSolicited] DEFAULT ((0)),
[CreatedByUserKey] [uniqueidentifier] NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[AccessKey] [uniqueidentifier] NOT NULL,
[CampaignKey] [uniqueidentifier] NULL,
[MarkedForDeleteOn] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
CREATE  TRIGGER [dbo].[asi_AppealMain_Delete]
    ON [dbo].[AppealMain]
    FOR DELETE
AS
UPDATE cp1 SET    cp1.TotalRevenue = cp1.TotalRevenue - deleted.TotalRevenue
    FROM CampaignMain cp1 INNER JOIN vBoAppeal ap1 ON cp1.CampaignKey = ap1.CampaignKey
                          INNER JOIN deleted ON ap1.AppealKey = deleted.AppealKey

GO
CREATE  TRIGGER [dbo].[asi_AppealMain_Insert_Update]
    ON [dbo].[AppealMain]
    FOR INSERT, UPDATE
AS
UPDATE cp1 SET    cp1.TotalRevenue = Coalesce(
        (SELECT    Sum(ap2.TotalRevenue)
         FROM    CampaignMain cp2
                INNER JOIN vBoAppeal ap2 ON cp2.CampaignKey = ap2.CampaignKey
         WHERE    cp2.CampaignKey = cp1.CampaignKey), 0)
FROM CampaignMain cp1 INNER JOIN vBoAppeal ap1 ON cp1.CampaignKey = ap1.CampaignKey
                      INNER JOIN inserted ON ap1.AppealKey = inserted.AppealKey

GO
ALTER TABLE [dbo].[AppealMain] ADD CONSTRAINT [PK_AppealMain] PRIMARY KEY CLUSTERED ([AppealKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AppealMain_AccessKey] ON [dbo].[AppealMain] ([AccessKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AppealMain_AppealStatusCode] ON [dbo].[AppealMain] ([AppealStatusCode]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AppealMain_CampaignKey] ON [dbo].[AppealMain] ([CampaignKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AppealMain_CreatedByUserKey] ON [dbo].[AppealMain] ([CreatedByUserKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AppealMain_UpdatedByUserKey] ON [dbo].[AppealMain] ([UpdatedByUserKey]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AppealMain] ADD CONSTRAINT [FK_AppealMain_AccessMain] FOREIGN KEY ([AccessKey]) REFERENCES [dbo].[AccessMain] ([AccessKey])
GO
ALTER TABLE [dbo].[AppealMain] ADD CONSTRAINT [FK_AppealMain_AppealStatusRef] FOREIGN KEY ([AppealStatusCode]) REFERENCES [dbo].[AppealStatusRef] ([AppealStatusCode])
GO
ALTER TABLE [dbo].[AppealMain] ADD CONSTRAINT [FK_AppealMain_CampaignMain] FOREIGN KEY ([CampaignKey]) REFERENCES [dbo].[CampaignMain] ([CampaignKey]) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AppealMain] ADD CONSTRAINT [FK_AppealMain_UniformRegistry] FOREIGN KEY ([AppealKey]) REFERENCES [dbo].[UniformRegistry] ([UniformKey])
GO
ALTER TABLE [dbo].[AppealMain] ADD CONSTRAINT [FK_AppealMain_UserMain_CreatedBy] FOREIGN KEY ([CreatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO
ALTER TABLE [dbo].[AppealMain] ADD CONSTRAINT [FK_AppealMain_UserMain_UpdatedBy] FOREIGN KEY ([UpdatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO